package com.egao.common.manage;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.egao.common.core.utils.ExcelUtils;
import com.egao.common.core.web.JsonResult;
import com.egao.common.module.statistics.entity.Inten;
import com.egao.common.module.statistics.entity.Status;
import com.egao.common.module.student.constants.StudentConstants;
import com.egao.common.module.student.entity.Student;
import com.egao.common.module.student.entity.StudentSignStatus;
import com.egao.common.module.student.entity.vo.StudentSignStatusTendVO;
import com.egao.common.module.student.entity.vo.StudentSignStatusVO;
import com.egao.common.module.student.entity.vo.StudentStatusExcelVO;
import com.egao.common.module.student.mapper.StudentSignStatusMapper;
import com.egao.common.module.student.service.StudentService;
import com.egao.common.module.system.constants.DictConstants;
import com.egao.common.module.system.entity.Dict;
import com.egao.common.module.system.service.DictService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import javax.xml.crypto.Data;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.util.stream.Collectors;

import static cn.hutool.core.util.NumberUtil.roundStr;


/**
 * 统计服务
 *
 * @author lym
 */
@Service
public class StatisticsServiceImpl {

    @Autowired
    private StudentService studentService;
    @Autowired
    private DictService dictService;
    @Autowired(required = false)
    private StudentSignStatusMapper studentSignStatusMapper;

    private final Logger logger = LoggerFactory.getLogger(StatisticsServiceImpl.class.getName());

    public JsonResult inten(Integer year, String colleges) {
        List<String> bjList = new ArrayList<>();
        if (!StrUtil.isBlank(colleges) && colleges.contains("b_")) {
            String[] collegeArr = colleges.split(",");
            for (String bjhm : collegeArr) {
                if (bjhm.contains("b_")) {
                    bjList.add(bjhm.split("b_")[1]);
                }
            }
            colleges = "";
        }

        List<Student> studentList = studentService.list(new LambdaQueryWrapper<Student>()
                .select(Student::getXl, Student::getDegree, Student::getQuestionnaireState, Student::getInten)
                .eq(Student::getYearId, year)
                .in(!StrUtil.isBlank(colleges), Student::getCollegeCode, colleges.split(","))
                .in(bjList.size() > 0, Student::getBjhm, bjList)
        );

        int studentTotal = studentList.size();

        List<Student> intenList = studentList.stream().filter(d -> StudentConstants.STUDENT_QUESTIONNAIRE_SUBMIT.equals(d.getQuestionnaireState())).collect(Collectors.toList());
        int intenTotal = intenList.size();

        List<Inten> list = new ArrayList<>();

        long qzTotal = intenList.stream().filter(d -> !StrUtil.isBlank(d.getInten()) && d.getInten().contains("求职")).count();
        Double qzRatio = intenTotal != 0 ? NumberUtil.div(qzTotal, intenTotal, 4) : 0.00;
        Inten qz = new Inten("求职", qzTotal, NumberUtil.decimalFormat("#.##%", qzRatio));
        list.add(qz);

        long sxTotal = intenList.stream().filter(d -> !StrUtil.isBlank(d.getInten()) && d.getInten().contains("升学")).count();
        Double sxRatio = sxTotal != 0 ? NumberUtil.div(sxTotal, intenTotal, 4) : 0.00;
        Inten sx = new Inten("升学(出国出境)", sxTotal, NumberUtil.decimalFormat("#.##%", sxRatio));
        list.add(sx);

        long kgTotal = intenList.stream().filter(d -> !StrUtil.isBlank(d.getInten()) && d.getInten().contains("考公")).count();
        Double kgRatio = kgTotal != 0 ? NumberUtil.div(kgTotal, intenTotal, 4) : 0.00;
        Inten kg = new Inten("考公", kgTotal, NumberUtil.decimalFormat("#.##%", kgRatio));
        list.add(kg);

        List<Student> intenBkList = studentList.stream().filter(d -> StudentConstants.STUDENT_QUESTIONNAIRE_SUBMIT.equals(d.getQuestionnaireState())
        && StudentConstants.STUDENT_DEGREE_BK.equals(d.getDegree())).collect(Collectors.toList());
        int intenBkTotal = intenBkList.size();
        long kyTotal = intenList.stream().filter(d -> !StrUtil.isBlank(d.getInten()) && d.getInten().contains("考研")).count();
        Double kyRatio = intenBkTotal != 0 ? NumberUtil.div(kyTotal, intenBkTotal, 4) : 0.00;
        Inten ky = new Inten("考研", kyTotal, NumberUtil.decimalFormat("#.##%", kyRatio));
        list.add(ky);

        return JsonResult.ok().setData(list).put("studentTotal", studentTotal).put("intenTotal", intenTotal);
    }

    public JsonResult status(Integer year, String colleges) {
        List<Student> studentList = studentService.list(new QueryWrapper<Student>()
                .select("count(1) id, sign_status_label")
                .eq("year_id", year)
                .in(!StrUtil.isBlank(colleges), "college_code", colleges.split(","))
                .groupBy("sign_status_label")
        );

        //查询缓存字典
        List<Dict> listDict = dictService.cacheList(DictConstants.SIGN_STATUS);

        if (listDict.size() == 0) {
            return JsonResult.error("没有字典项");
        }

        //获取总数
        int sum = 0;
        for (Student student : studentList) {
            sum += student.getId();
        }

        int non = 0;

        List<Status> listStatus = new ArrayList<>();
        for (Dict dict : listDict) {
            boolean b = false;
            for (Student student : studentList) {
                if (dict.getLabel().equals(student.getSignStatusLabel())) {
                    b = true;
                    listStatus.add(new Status(dict.getLabel(), student.getId(), ratio(student.getId(), sum)));
                    continue;
                }
                if (StrUtil.isBlank(student.getSignStatusLabel())) {
                    non = student.getId();
                }
            }
            if (!b) {
                listStatus.add(new Status(dict.getLabel(), 0, ratio(0, sum)));
            }
        }
        listStatus.add(new Status("未登记", non, ratio(non, sum)));
        Collections.sort(listStatus, new Comparator<Status>() {
            @Override
            public int compare(Status o1, Status o2) {
                // 返回值为int类型，大于0表示正序，小于0表示逆序
                return o2.getNum() - o1.getNum();
            }
        });


        return JsonResult.ok().setData(listStatus).put("sum", sum);
    }

    private String ratio(Integer num, Integer sum) {
        double dob = ((double) num / sum * 100);
        if (dob > 0 && dob < 0.01) {
            dob = 0.01;
        }
        return roundStr(dob, 2) + "%";
    }

    public void exportStatus(Integer year, String colleges, HttpServletResponse response) {
        JsonResult jsonResult = status(year, colleges);
        if (jsonResult.getCode() == 1) {
            return;
        }
        List<Status> listStatus = (List<Status>) jsonResult.getData();

        // 导出excel模板
        ExcelWriter writer = ExcelUtil.getWriter(true);

        // 设置别名和排序
        excelAddHeaderAlias(writer);

        // 写出内容 xlsx
        writer.write(listStatus, true);

        // 设置列自适应宽 必须在write以后
        ExcelUtils.autoColumnMaxSizeAll(writer.getSheet());

        String fileName = "status-" + DateUtil.format(new Date(), DatePattern.PURE_DATE_PATTERN) + ".xlsx";

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

        try {
            OutputStream out = response.getOutputStream();
            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (IOException e) {
            writer.close();
            logger.error("导出失败 - StatisticsServiceImpl - exportStatus", e);
        } finally {
            writer.close();
        }
    }

    public void excelAddHeaderAlias(ExcelWriter writer) {
        writer.addHeaderAlias("name", "状态");
        writer.addHeaderAlias("num", "人数");
        writer.addHeaderAlias("ratio", "比例");
    }

    public JsonResult tendStatus(Integer year) {
        JsonResult jsonResult = status(year, "");
        if (jsonResult.getCode() == 1) {
            return jsonResult;
        }

        List<Status> listStatus = (List<Status>) jsonResult.getData();
        Integer sum = (Integer) jsonResult.get("sum");

        //处理第一周数据(判断若无数据则添加第一周数据)
        List<StudentSignStatus> listSignStatus = studentSignStatusMapper.selectList(null);

        List<Dict> listDict = dictService.cacheList(DictConstants.SIGN_STATUS);

        if (listSignStatus.size() > 0) {
            //添加同比增长率

            return increment(listStatus, sum, false);
        }
        return increment(listStatus, sum, true);

    }

    /**
     * 同比增长率计算
     *
     * @return
     */
    private JsonResult increment(List<Status> listStatus, Integer sum, boolean b) {
        Date newDate = new Date();
        Date beginOfDay = DateUtil.beginOfDay(newDate);
        DateTime lastWeek = DateUtil.beginOfDay(DateUtil.lastWeek());
        if (b) {
            for (Status status : listStatus) {
                StudentSignStatus s = new StudentSignStatus();
                s.setTend("0");
                if (status.getNum() == 0) {
                    s.setIncrement("0%");
                } else {
                    s.setIncrement("100%");
                }
                s.setRatio(status.getRatio());
                s.setSum(sum);
                s.setNum(status.getNum());
                s.setEndTime(beginOfDay);
                s.setType(status.getName());
                studentSignStatusMapper.insert(s);
            }
            return JsonResult.ok("更新成功");
        }

        QueryWrapper<StudentSignStatus> wrapper = new QueryWrapper<>();
        wrapper.eq("end_time", lastWeek);
        List<StudentSignStatus> listLastWeekSignStuatus = studentSignStatusMapper.selectList(wrapper);
        if (listLastWeekSignStuatus.size() == 0) {
            return JsonResult.error("结果不存在");
        }

        for (StudentSignStatus lastWeekStatus : listLastWeekSignStuatus) {
            for (Status s : listStatus) {
                if (s.getName().equals(lastWeekStatus.getType())) {
                    StudentSignStatus newStatus = new StudentSignStatus();
                    newStatus.setStartTime(lastWeekStatus.getEndTime());
                    newStatus.setEndTime(beginOfDay);
                    newStatus.setRatio(s.getRatio());
                    newStatus.setSum(sum);
                    newStatus.setType(s.getName());
                    //同比增加
                    if (lastWeekStatus.getNum() < s.getNum()) {
                        String increment = ratio((s.getNum() - lastWeekStatus.getNum()), lastWeekStatus.getNum());
                        newStatus.setTend("0");
                        newStatus.setIncrement(increment);
                    } else if (lastWeekStatus.getNum() > s.getNum()) {
                        String increment = ratio((lastWeekStatus.getNum() - s.getNum()), lastWeekStatus.getNum());
                        newStatus.setTend("1");
                        newStatus.setIncrement(increment);
                    } else {
                        newStatus.setTend("2");
                        newStatus.setIncrement("0%");
                    }
                    studentSignStatusMapper.insert(newStatus);
                }
            }
        }
        return JsonResult.ok("更新成功");
    }

    public JsonResult statisticsTend() {
        //根据时间进行分类
        QueryWrapper<StudentSignStatus> groupWrapper = new QueryWrapper<>();
        groupWrapper.select("id", "end_time", "start_time", "sum").groupBy("end_time");
        List<StudentSignStatus> listGroupStatus = studentSignStatusMapper.selectList(groupWrapper);

        //查询全部
        List<StudentSignStatus> listAllStatus = studentSignStatusMapper.selectList(null);

        List<StudentSignStatusTendVO> listTendVO = new ArrayList<>();

        for (StudentSignStatus groupStatus : listGroupStatus) {

            //先将共有不变化的字段存储
            StudentSignStatusTendVO tendVO = new StudentSignStatusTendVO();
            List<StudentSignStatusTendVO> listChildVO = new ArrayList<>();
            tendVO.setId(groupStatus.getId());
            tendVO.setEndTime(groupStatus.getEndTime());
            tendVO.setStartTime(groupStatus.getStartTime());
            tendVO.setSum(groupStatus.getSum());

            for (StudentSignStatus tendStatus : listAllStatus) {
                //储存变化的字段,
                if (tendStatus.getEndTime().compareTo(groupStatus.getEndTime()) == 0) {
                    StudentSignStatusTendVO childVO = new StudentSignStatusTendVO();
                    childVO.setNum(tendStatus.getNum());
                    childVO.setName(tendStatus.getType());
                    childVO.setRatio(tendStatus.getRatio());
                    childVO.setTend(tendStatus.getTend());
                    childVO.setIncrement(tendStatus.getIncrement());
                    listChildVO.add(childVO);
                }
            }
            //封装到子类集合中
            tendVO.setListTendVO(listChildVO);

            //最后封装进需要返回的集合
            listTendVO.add(tendVO);
        }
        return JsonResult.ok().setData(listTendVO);
    }

    public void exportTend(HttpServletResponse response) {
        JsonResult jsonResult = statisticsTend();
        if (jsonResult.getCode() == 1) {
            return;
        }

        List<StudentSignStatusTendVO> listStatus = (List<StudentSignStatusTendVO>) jsonResult.getData();
        List<StudentStatusExcelVO> listExcelVO = new ArrayList<>();

        //拼接周期字符串
        for (StudentSignStatusTendVO statusTendVO : listStatus) {
            StudentStatusExcelVO studentStatusExcelVO = new StudentStatusExcelVO();
            String startFormat = DateUtil.format(statusTendVO.getStartTime(), "yyyy-MM-dd");
            String endFormat = DateUtil.format(statusTendVO.getEndTime(), "yyyy-MM-dd");
            studentStatusExcelVO.setCycle((StrUtil.isBlank(startFormat) ? "" : startFormat) + "~" + (StrUtil.isBlank(endFormat) ? "" : endFormat));
            studentStatusExcelVO.setSum(statusTendVO.getSum());

            //拼接内容为（人数-占比-同比）
            for (StudentSignStatusTendVO s : statusTendVO.getListTendVO()) {
                if ("未登记".equals(s.getName())) {
                    studentStatusExcelVO.setData1(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("已签约".equals(s.getName())) {
                    studentStatusExcelVO.setData2(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("升学(境内)".equals(s.getName())) {
                    studentStatusExcelVO.setData3(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("计划继续找工作".equals(s.getName())) {
                    studentStatusExcelVO.setData4(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("考研复试中".equals(s.getName())) {
                    studentStatusExcelVO.setData5(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("计划出国出境".equals(s.getName())) {
                    studentStatusExcelVO.setData6(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("计划继续考研".equals(s.getName())) {
                    studentStatusExcelVO.setData7(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("计划继续考公务员".equals(s.getName())) {
                    studentStatusExcelVO.setData8(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("延期毕业".equals(s.getName())) {
                    studentStatusExcelVO.setData9(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("公务员复试中".equals(s.getName())) {
                    studentStatusExcelVO.setData10(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("有offer未签约".equals(s.getName())) {
                    studentStatusExcelVO.setData11(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("没有明确计划".equals(s.getName())) {
                    studentStatusExcelVO.setData12(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("定向委培".equals(s.getName())) {
                    studentStatusExcelVO.setData13(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }
                if ("计划创业".equals(s.getName())) {
                    studentStatusExcelVO.setData14(s.getNum() + "-" + s.getRatio() + "-" + s.getIncrement() + ("0".equals(s.getTend()) ? '↑' : "1".equals(s.getTend()) ? '↓' : ""));
                    continue;
                }

            }

            listExcelVO.add(studentStatusExcelVO);
        }

        // 导出excel模板
        ExcelWriter writer = ExcelUtil.getWriter(true);

        // 设置别名和排序
        excelAddTend(writer);

        // 写出内容 xlsx
        writer.write(listExcelVO, true);

        // 设置列自适应宽 必须在write以后
        ExcelUtils.autoColumnMaxSizeAll(writer.getSheet());

        String fileName = "tend-" + DateUtil.format(new Date(), DatePattern.PURE_DATE_PATTERN) + ".xlsx";

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

        try {
            OutputStream out = response.getOutputStream();
            writer.flush(out, true);
            writer.close();
            IoUtil.close(out);
        } catch (IOException e) {
            writer.close();
            logger.error("导出失败 - StatisticsServiceImpl - exportTendStatus", e);
        } finally {
            writer.close();
        }
    }

    public void excelAddTend(ExcelWriter writer) {
        writer.addHeaderAlias("cycle", "周期");
        writer.addHeaderAlias("sum", "总人数");
        writer.addHeaderAlias("data1", "未登记（人数-占比-同比）");
        writer.addHeaderAlias("data2", "已签约");
        writer.addHeaderAlias("data3", "升学(境内)");
        writer.addHeaderAlias("data4", "计划继续找工作");
        writer.addHeaderAlias("data5", "考研复试中");
        writer.addHeaderAlias("data6", "计划出国出境");
        writer.addHeaderAlias("data7", "计划继续考研");
        writer.addHeaderAlias("data8", "计划继续考公务员");
        writer.addHeaderAlias("data9", "延期毕业");
        writer.addHeaderAlias("data10", "公务员复试中");
        writer.addHeaderAlias("data11", "有offer未签约");
        writer.addHeaderAlias("data12", "没有明确计划");
        writer.addHeaderAlias("data13", "定向委培");
        writer.addHeaderAlias("data14", "计划创业");
    }
}
